﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Data;
using Common;

namespace DAL
{
    public class DQConfigDAL : DB
    {

        public DQConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 更新dq数据
        /// </summary>
        /// <param name="data">提交数据</param>
        /// <returns></returns>
        public int Update(DQConfigTransData data)
        {
            HIDB.dbo conn = base.conn();
            try
            {
                conn.TranBegin();//事务开始
                if (string.IsNullOrEmpty(data.DQConfig.GUID))
                {
                    data.DQConfig.GUID = Guid.NewGuid().ToString();

                    if (!this.Insert(data.DQConfig, conn))//添加失败，事务回滚
                    {
                        conn.TranRollback();
                        return 0;
                    }
                }
                else
                {
                    if (!this.Update(data.DQConfig, conn))//更新失败，事务回滚
                    {
                        conn.TranRollback();
                        return 0;
                    }
                }
                //删除旧记录 
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@DQ_ID", data.DQConfig.GUID) };
                conn.ExecuteSql("delete [Working_db].[DQ_Rules_Config] where [DQ_ID] = @DQ_ID", sqlparams);

                DQRulesConfigDAL dqRulesConfigDAL = new DQRulesConfigDAL(loginInfo);
                foreach (var item in data.DQRulesConfigList)
                {
                    item.GUID = Guid.NewGuid().ToString();
                    item.DQ_ID = data.DQConfig.GUID;
                    item.LegalOperator = string.IsNullOrEmpty(item.LegalOperator) ? DBNull.Value.ToString() : item.LegalOperator;
                    if (string.IsNullOrEmpty(item.LegalOperator))
                    {
                        item.LegalRules = DBNull.Value.ToString();
                    }
                    else
                    {
                        item.LegalRules = string.IsNullOrEmpty(item.LegalRules) ? DBNull.Value.ToString() : item.LegalRules;
                    }
                    item.ILegalOperator = string.IsNullOrEmpty(item.ILegalOperator) ? DBNull.Value.ToString() : item.ILegalOperator;
                    if (string.IsNullOrEmpty(item.ILegalOperator))
                    {
                        item.IllegalRules = DBNull.Value.ToString();
                    }
                    else
                    {
                        item.IllegalRules = string.IsNullOrEmpty(item.IllegalRules) ? DBNull.Value.ToString() : item.IllegalRules;
                    }
                    if (!dqRulesConfigDAL.Insert(item, conn))
                    {
                        conn.TranRollback();
                        return 0;
                    }
                }

                //执行脚本
                SqlParameter[] sp = new SqlParameter[]
                {
                     new SqlParameter("@schemaName_p",data.DQConfig.SchemaName)
                    ,new SqlParameter("@tbName_p",data.DQConfig.TableName)
                    ,new SqlParameter("@dq_id_p",data.DQConfig.GUID)
                };
                //DataTable dqdt = conn.StoredDataTable("[Working_db].[CreateFactFromOdsByDq]", sp);
                //int retVal = Convert.ToInt32(dqdt.Rows[0][0]);
                //if (retVal == 0)
                //{
                //    conn.TranRollback();
                //    return false;
                //}
                conn.TranCommit();//提交事务
                return 1;
            }
            catch (Exception)
            {
                return 0;
                throw;
            }
        }

        private bool Insert(DQConfig data, HIDB.dbo conn)
        {
            bool result = true;
            try
            {
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@GUID",data.GUID) 
                    ,new SqlParameter("@ModelID",data.ModelID)
                    ,new SqlParameter("@DQ_Name",data.DQ_Name)
                    ,new SqlParameter("@DQ_Description",data.DQ_Description) 
                    ,new SqlParameter("@SchemaName",data.SchemaName) 
                    ,new SqlParameter("@TableName",data.TableName) 
                };
                conn.ExecuteSql(@"
declare @sortNum int
select top 1 @sortNum=SortNum from [Working_db].[DQ_Config] where modelid=@ModelID order by SortNum desc;
insert into [Working_db].[DQ_Config]([GUID],ModelID,DQ_Name, DQ_Description,SchemaName,TableName,SortNum,CreateTime, IsDelete)	
values(@GUID,@ModelID,@DQ_Name,@DQ_Description,@SchemaName,@TableName,@sortNum+1,GETDATE(),0)", sqlparams);
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        private bool Update(DQConfig data, HIDB.dbo conn)
        {
            bool result = true;
            try
            {
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new SqlParameter("@GUID",data.GUID) 
                    ,new SqlParameter("@DQ_Name",data.DQ_Name)
                    ,new SqlParameter("@DQ_Description",data.DQ_Description) 
                };
                conn.ExecuteSql("update [Working_db].[DQ_Config] set DQ_Name = @DQ_Name,DQ_Description = @DQ_Description where [GUID] = @GUID", sqlparams);
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        public int Delete(string id)
        {
            HIDB.dbo conn = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@GUID",id)
                };
                conn.ExecuteSql(@"exec[Working_db].[DropDqTable] @GUID; 
update [Working_db].[DQ_Config] set sortnum=sortnum-1 where ModelID=(
select modelid from [Working_db].[DQ_Config] where GUID=@GUID
) and sortnum>(select SortNum from [Working_db].[DQ_Config] where GUID=@GUID)
	delete Working_db.DQ_Rules_Config where DQ_ID=@GUID;
	delete Working_db.DQ_Config where [GUID]=@GUID;", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool UpStep(IList<DQConfig> jsonList, string id)
        {
            bool result = true;
            HIDB.dbo conn = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@GUID",id)
                };
                conn.ExecuteSql("Update [Working_db].[Model_Config] set Step=3 where GUID=@GUID and Step=2", sqlparams);
                foreach (DQConfig dq in jsonList)
                {
                    sqlparams = new SqlParameter[]
                    { 
                         new  SqlParameter("@GUID",dq.GUID),
                         new  SqlParameter("@SortNum",dq.SortNum)
                    };
                    conn.ExecuteSql("Update [Working_db].[DQ_Config] set SortNum=@SortNum where GUID=@GUID", sqlparams);
                }
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        public string CheckDQColumn(string id)
        { 
            HIDB.dbo conn = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@ModelID",id)
                };
                DataTable dt = conn.GetDataTable(@"select r.ColumnName,d.DQ_Name from [Working_db].[DQ_Rules_Config] r 
inner join [Working_db].[DQ_Config] d on r.DQ_ID=d.GUID where d.ModelID=@ModelID order by d.DQ_Name", sqlparams);
                DataTable dt_ods = conn.GetDataTable("select STG_TB_Name from [Working_db].[ODS_STG_Config] where ODS_ID=@ModelID", sqlparams);


                foreach (DataRow dr in dt.Rows)
                {
                    string dq_ColName = dr[0].ToString().ToLower();
                    bool isExt = false;
                    foreach (DataRow dr_ods in dt_ods.Rows)
                    {
                        if (dq_ColName.IndexOf(dr_ods[0].ToString().ToLower()) > -1)
                        {
                            isExt = true;
                            break;
                        }
                    }
                    if (!isExt) {
                        return dr[1].ToString();
                    }
                }
                return "1";
            }
            catch (Exception)
            { 
                throw;
            } 
        }
        public bool SendDQConfig(string modelid, string dbname, string nmodelid, string ndbname)
        {
            bool result = true;
            HIDB.dbo conn = base.conn();
            try
            {
                dbname = "[" + dbname + "]";
                ndbname = "[" + ndbname + "]";
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@modelid",modelid)
                     ,new  SqlParameter("@dbname",dbname)
                     ,new  SqlParameter("@nmodelid",nmodelid)
                     ,new  SqlParameter("@ndbname",ndbname)
                };
                conn.ExecuteSql(string.Format(@"
DECLARE @i INT = 1;
	DECLARE @dqCount INT = (SELECT COUNT(1) FROM {0}.[Working_db].[DQ_Config] WHERE ModelID=@modelid);
	delete from {1}.[Working_db].[DQ_Config] where modelid=@nmodelid
	WHILE(@i<=@dqCount)
	BEGIN
		declare @ndqid varchar(50)=newid()
		 DECLARE @dqid VARCHAR(50)=(SELECT guid FROM (SELECT ROW_NUMBER() OVER(ORDER BY createtime) AS id, (GUID) AS guid FROM																{0}.Working_db.[DQ_Config] WHERE ModelID=@modelid) t WHERE id =@i  );
		 insert into {1}.[Working_db].[DQ_Config](GUID,ModelID,SchemaName,TableName,DQ_Name,DQ_Description,SortNum,CreateTime)
		 select @ndqid,@nmodelid,SchemaName,'ODS_'+@nmodelid,DQ_Name,DQ_Description,SortNum,GETDATE() from {0}.[Working_db].[DQ_Config]  where GUID=@dqid;

		 insert into {1}.[Working_db].[DQ_Rules_Config]([GUID],[DQ_ID],[ColumnName],[DisplayName],[ColumnDesc],[ILegalOperator],[IllegalRules],[UniqueConstraint],[CreateTime])
		 select newid(),[DQ_ID],[ColumnName],[DisplayName],[ColumnDesc],[ILegalOperator],[IllegalRules],[UniqueConstraint],[CreateTime] from {0}.[Working_db].[DQ_Rules_Config] where dq_id=@dqid;
		 update {1}.[Working_db].[DQ_Rules_Config] set dq_id=@ndqid where dq_id=@dqid

		SELECT @i=@i+1;
	END
", dbname, ndbname), sqlparams);

            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }
        public bool DQNameIsExist(string dqName, string id, string modelID)
        {
            bool result = false;
            //创建数据库对象
            HIDB.dbo con = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@DQ_Name",dqName)
                    ,new  SqlParameter("@ModelID",modelID)
                    ,new  SqlParameter("@GUID",string.IsNullOrEmpty(id)?DBNull.Value.ToString():id)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_DQ_Config_DQName_IsExist]", sqlparams);
                result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString()) > 0 ? true : false;
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        public IList<DQConfig> GetList(SqlParameter[] pare, string sort, string order)
        {
            IList<DQConfig> result = new List<DQConfig>();
            try
            {
                HIDB.dbo conn = base.conn();

                DataTable dt = conn.GetDataTable(string.Format("SELECT GUID,DQ_Name,DQ_Description,SortNum,CreateTime FROM [Working_db].[DQ_Config] where ModelID=@ModelID order by {0} {1}", sort, order), pare);
                result = dt.MapToList<DQConfig>();

            }
            catch (Exception)
            {
                result = null;
                throw;
            }
            return result;
        }

        public DQRulesConfig GetRoleList(string id)
        {
            IList<DQRulesConfig> result = new List<DQRulesConfig>();
            try
            {
                HIDB.dbo conn = base.conn();
                SqlParameter[] pare = new SqlParameter[] { 
                 new SqlParameter("@GUID",id) 
                };
                DataTable dt = conn.GetDataTable(string.Format("select * from [Working_db].[DQ_Rules_Config] where GUID=@GUID"), pare);
                result = DBConvert.DataTableToList<DQRulesConfig>(dt);

            }
            catch (Exception)
            {
                throw;
            }
            if (result.Count > 0)
                return result[0];
            else
                return new DQRulesConfig();
        }
        public int GetListCount(string schemaName, string tableName)
        {
            int result = 0;
            try
            {
                HIDB.dbo conn = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@SchemaName",string.IsNullOrEmpty(schemaName)?DBNull.Value.ToString():schemaName)
                    ,new  SqlParameter("@TableName",string.IsNullOrEmpty(tableName)?DBNull.Value.ToString():tableName)
                };
                DataTable dt = conn.StoredDataTable("[Working_db].[UP_DQ_Config_GetListCount]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = int.Parse(dt.Rows[0]["RowsCount"].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public DQConfig GetItem(string id)
        {
            DQConfig result = new DQConfig();
            try
            {
                HIDB.dbo conn = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GUID", id) };
                DataTable dt = conn.StoredDataTable("[Working_db].[UP_DQ_Config_GetItem]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow item in dt.Rows)
                    {
                        result.GUID = item["GUID"].ToString();
                        result.SchemaName = item["SchemaName"].ToString();
                        result.TableName = item["TableName"].ToString();
                        result.DQ_Name = item["DQ_Name"].ToString();
                        result.DQ_Description = item["DQ_Description"].ToString();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public int GetDQConfigCount(string tableName)
        {
            int result = 0;
            try
            {
                HIDB.dbo con = base.conn();
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@tableName", tableName) };
                result = int.Parse(con.GetScalar("select count(1) from [Working_db].[DQ_Config] where [TableName] = @tableName", sqlparams));
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public IList<string> GetStgTabList(string tableName)
        {
            IList<string> result = new List<string>();
            tableName = tableName.Replace("ODS_", "");
            string sql = @"select STG_TB_Name from Working_db.ODS_STG_Config  where ODS_ID=  (select [guid] from Working_db.ODS_Config a where  a.ODS_TableName = @tablename)";
            HIDB.dbo con = base.conn();
            DataTable dt = con.GetDataTable(sql, new SqlParameter[] { new SqlParameter("@tablename", tableName) });
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    result.Add(item[0].ToString());
                }
            }
            return result;
        }
    }
}
